﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using CrystalDecisions.CrystalReports.Engine;

namespace Project_Udrinkwater.Empoyee_Management
{
    public partial class LoanReport : System.Web.UI.Page
    {
        
        private string Connection = ConfigurationManager.ConnectionStrings["DB_Udrink"].ConnectionString;
        protected void Page_Load(object sender, EventArgs e)
        {
            
            SqlConnection objConn = new SqlConnection();
            SqlCommand objCmd = new SqlCommand();
            SqlDataAdapter dtAdapter = new SqlDataAdapter();

            DataSet ds = new DataSet();
            DataTable dt = null;
            string strConnString = null;
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append(@"SELECT                    TB_Loan.Loan_doc_NO, TB_Loan.Loan_Date,
                          TB_Employee.FirstName+ ' ' +TB_Employee.LastName As Emp,
                          TB_Customer.Firstname+' '+ TB_Customer.Lastname As Cus,
                          ROW_NUMBER() OVER (ORDER BY TB_Loan_List.ID Asc) AS RowID, 
                           TB_Product.Product_Name,
                          TB_Loan_Category.Loan_Name,
						  TB_Loan_List.Loan_Amount,  
                          TB_Order_Category.Price, 
                          TB_Loan_List.Loan_Amount * TB_Loan_List.Loan_Unit * TB_Order_Category.Price AS Total                                                  
                           FROM                      TB_Loan INNER JOIN
                          TB_Loan_List ON TB_Loan.Loan_ID = TB_Loan_List.Loan_ID INNER JOIN
                          TB_Loan_Category ON TB_Loan_List.Loan_Unit = TB_Loan_Category.Loan_Unit INNER JOIN
                          TB_Product ON TB_Loan_List.Product_ID = TB_Product.Product_ID INNER JOIN
                          TB_Product_Category ON TB_Product.Product_Category_ID = TB_Product_Category.Product_Category_ID INNER JOIN
                          TB_Customer ON TB_Loan.Customer_ID = TB_Customer.Customer_ID INNER JOIN
                          TB_Employee ON TB_Loan.Employee_ID = TB_Employee.Employee_ID INNER JOIN
                          TB_Order_Category ON TB_Product.Product_ID = TB_Order_Category.Product_ID ");
            strSQL.Append(" WHERE TB_Order_Category.Order_Category_Name = 'ยืม-คืน' AND TB_Loan.Loan_ID = " + Session["Loan_ID"].ToString() + "");
            
            objConn.ConnectionString = Connection;
            var _with1 = objCmd;
            _with1.Connection = objConn;
            _with1.CommandText = strSQL.ToString();
            _with1.CommandType = CommandType.Text;
            dtAdapter.SelectCommand = objCmd;

            dtAdapter.Fill(ds, "CrystalReportLoan");
            dt = ds.Tables[0];
           
            dtAdapter = null;
            objConn.Close();
            objConn = null;

            ReportDocument rpt = new ReportDocument();
            rpt.Load(Server.MapPath("~/Empoyee_Management/CrystalReportLoan.rpt"));
            rpt.SetDataSource(dt);
            rpt.SetParameterValue("Loan_doc_NO", dt.Rows[0]["Loan_doc_NO"].ToString());
            rpt.SetParameterValue("Loan_Date", dt.Rows[0]["Loan_Date"].ToString());
            rpt.SetParameterValue("Emp", dt.Rows[0]["Emp"].ToString());
            rpt.SetParameterValue("Cus", dt.Rows[0]["Cus"].ToString());
            this.CrystalReportLoan.ReportSource = rpt;
        }
    }
}